/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */


package ebookmgr;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author mazhao
 */
public class DatabaseUtil {

    private static final String HSQLDB_DRIVER = "org.hsqldb.jdbc.JDBCDriver";
    private static final String HSQLDB_URL = "jdbc:hsqldb:file:" + Configuration.getDatabasePath();
    private static final String HSQLDB_USER = "SA";
    private static final String HSQLDB_PWD = "";
    

    private static final String CMD_SHUTDOWN = "shutdown;";

    private static final String CMD_CREATE_TABLE =
            "Create Table BOOK_INF(" +
            " PATH varchar(500) not null, " +
            " NAME varchar(500) not null," +
            " STAR int "
            +")";

    private static final String CMD_QUERY_STAR =
            " Select STAR From BOOK_INF Where PATH = ? and NAME = ? ";

    private static final String CMD_INSERT_BOOK =
            " Insert Into BOOK_INF(PATH, NAME, STAR) Values(?, ?, ?) ";

    private static final String CMD_UPDATE_STAR =
            " Update BOOK_INF Set STAR=? Where PATH=? and NAME=? ";

    private static void  safelyClose(Statement stmt) {
        if(stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                // nothing to do
            }
        }
    }

    private static void safelyClose(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                // nothing to do
            }
        }
    }

    private static void loadDriver() {
        try {
            Class.forName(HSQLDB_DRIVER).newInstance();
        } catch (Exception ex) {
            EBookMgrUtil.showMessageAndExit("Can not load hsqldb driver, message is:" + ex.getMessage());
        }
    }

    public static Connection connect() {
        System.out.println("connect!");
        loadDriver();
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(HSQLDB_URL, HSQLDB_USER, HSQLDB_PWD);
        } catch (SQLException ex) {
            EBookMgrUtil.showMessageAndExit("Can not get connection, message is:" + ex.getMessage());
        }
        return conn;
    }

    public static void disconnect(Connection conn) {

        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.execute(CMD_SHUTDOWN);
        } catch (SQLException ex) {
            EBookMgrUtil.showMessageAndExit("Can not close connection, message is:" + ex.getMessage());
        } finally {
            safelyClose(stmt);
            safelyClose(conn);
        }
    }

    private static void createTable(Connection conn) {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.execute(CMD_CREATE_TABLE);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUtil.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            safelyClose(stmt);
        }
    }


    public static void initDatabase() {
        File dbfile = new File(Configuration.getDatabaseFilePath());
        if (!dbfile.exists()) {
            Connection conn = connect();
            createTable(conn);
            disconnect(conn);
        }
    }

    public static int getStar(Connection conn, String folder, String name) {
        int star = 1;

        PreparedStatement pstmt = null;
        try {
            // query by path and name
            pstmt = conn.prepareStatement(CMD_QUERY_STAR);
            pstmt.setString(1, folder);
            pstmt.setString(2, name);
            ResultSet rs = pstmt.executeQuery();

            // if exist, then return
            if(rs.next()) {
                star = rs.getInt(1);
            } else {
                //else create default
                pstmt = conn.prepareStatement(CMD_INSERT_BOOK);
                pstmt.setString(1, folder);
                pstmt.setString(2, name);
                pstmt.setInt(3, star);
                pstmt.execute();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUtil.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            safelyClose(pstmt);
        }

        return star;
    }

    public static int updateStar(Connection conn, String folder, String name, int star) {
        PreparedStatement pstmt = null;
        try {
            // query by path and name
            pstmt = conn.prepareStatement(CMD_UPDATE_STAR);
            pstmt.setInt(1, star);
            pstmt.setString(2, folder);
            pstmt.setString(3, name);
            int ret = pstmt.executeUpdate();

            // if not exist, insert default
            if(ret == 0) {
                //else create default
                pstmt = conn.prepareStatement(CMD_INSERT_BOOK);
                pstmt.setString(1, folder);
                pstmt.setString(2, name);
                pstmt.setInt(3, star);
                pstmt.execute();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseUtil.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            safelyClose(pstmt);
        }

        return star;

    }
}
